﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;

namespace UdrinkWater_Project
{
    public class CheckTime
    {
        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        public DataTable CheckDayOfYear(string Year)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" declare @D1 date; ");
            strSQL.Append(" declare @D2 date; ");
            strSQL.Append(" set @D1 = '" + Year + "-01-01'; ");
            strSQL.Append(" set @D2 = '" + Year + "-12-31'; ");
            strSQL.Append(" with C(TheDate) as (select @D1 union all select dateadd(day, 1, C.TheDate) from C where C.TheDate < @D2) ");
            strSQL.Append("  select cast(dateadd(YEAR, datediff(YEAR, 0, C.TheDate), 0) as date) as YEAR, count(*) as DayCount from C ");
            strSQL.Append(" group by dateadd(YEAR, datediff(YEAR, 0, C.TheDate), 0) ");
            strSQL.Append(" option (maxrecursion 0) ");
            string sqltext = strSQL.ToString();
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetData_Employee()
        {
            string sqltext = @"SELECT [ID_Card] FROM TB_Employee";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_CheckCalculate()
        {
            string sqltext = @"SELECT * FROM [TB_Salary] WHERE [Salary_Start] BETWEEN CONVERT(DATE,DATEADD(s,+1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+0,0))) AND CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_EmployeeCommission()
        {
            string sqltext = @"SELECT [Employee_ID] FROM [TB_Employee] WHERE [Department_ID]=1 ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Absence(string Emp)
        {
            string sqltext = @"SELECT * FROM [TB_EmpCheckIn]
                             WHERE Employee_ID='"+Emp+"' AND Status_Check=2 AND CheckIn_Date BETWEEN CONVERT(DATE,DATEADD(s,+1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+0,0))) AND CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_OT(string Emp)
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT TB_Employee.ID_Card,TB_EmployeeOT_IN.CheckIN_Date,TB_EmployeeOT_IN.CheckIn_Time,TB_EmployeeOT_Out.CheckOut_Time
                               FROM TB_Employee INNER JOIN TB_EmployeeOT_IN ON TB_EmployeeOT_IN.Employee_ID=TB_Employee.ID_Card
                               INNER JOIN TB_EmployeeOT_Out ON TB_EmployeeOT_IN.Emp_Date=TB_EmployeeOT_Out.Emp_Date
                               WHERE TB_Employee.ID_Card='"+Emp+"' AND (TB_EmployeeOT_IN.CheckIN_Date BETWEEN CONVERT(DATE,DATEADD(s,+1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+0,0))) AND CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))))";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetData_Reduce_Withdraw(string Emp)
        {
            string sqltext = @"SELECT SUM(Num_WithDraw) AS TotalEmp FROM TB_WithDraw 
                               WHERE Employee_ID = '"+Emp+"' AND (Date_WithDraw BETWEEN CONVERT(DATE,DATEADD(s,+1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+0,0))) AND CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))))";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetData_Reduce_Commission(string Emp, int Num)
        {
            string sqltext = @"SELECT SUM(TB_Orderlist.Product_Count) AS TOTAL FROM TB_Order INNER JOIN TB_Orderlist ON TB_Order.Order_Date = TB_Orderlist.Order_Date
                               INNER JOIN TB_Product ON TB_Orderlist.Product_ID = TB_Product.Product_ID WHERE TB_Order.Employee_ID = '"+Emp+"' AND TB_Product.Unit="+Num+" AND (TB_Order.Order_Date BETWEEN CONVERT(DATE,DATEADD(s,+1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+0,0))) AND CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))))";

            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
    }
}